package com.hf.excel;

import com.hf.excel.handler.MySheetHandler;
import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.xml.parsers.ParserConfigurationException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * 目前只支持返回 List<List<String>>
 * @author hf
 * @Date 2019-05-16
 * @Time 10:44
 **/
public class ExcelReader {

    /**
     * 结果集
     */
    private List<List<String>> datas = new ArrayList<>();

    /**
     * 开始行
     */
    private int startRowNum;

    /**
     * 结束列
     */
    private int endCellNum;


    public ExcelReader(int startRowNum, int endCellNum) {
        this.startRowNum = startRowNum;
        this.endCellNum = endCellNum;
    }

    /**
     * 处理第一个sheet
     * @param filename
     * @throws Exception
     */
    public void processFirstSheet(String filename) throws Exception {
        try (OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ)) {
            parsePOCpackage(pkg);
        }
    }
    /**
     * 处理第一个sheet
     * @param in
     * @throws Exception
     */
    public void processInputStreamFirstSheet(InputStream in) throws Exception {
        try (OPCPackage pkg = OPCPackage.open(in)) {
            parsePOCpackage(pkg);
        }
    }

    /**
     * 解析
     * @param pkg
     * @throws Exception
     */
    private void parsePOCpackage(OPCPackage pkg) throws Exception{
        XSSFReader xssfReader = new XSSFReader(pkg);
        SharedStringsTable sst = xssfReader.getSharedStringsTable();

        XMLReader parser = fetchSheetParser(sst);

        // process the first sheet
        try (InputStream sheet = xssfReader.getSheetsData().next()) {
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
        }
    }

    /**
     * 处理所有的sheet
     * @param filename
     * @throws Exception
     */
    public void processAllSheets(String filename) throws Exception {
        try (OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ)) {
            XSSFReader r = new XSSFReader(pkg);
            SharedStringsTable sst = r.getSharedStringsTable();

            XMLReader parser = fetchSheetParser(sst);

            Iterator<InputStream> sheets = r.getSheetsData();
            while (sheets.hasNext()) {
                System.out.println("Processing new sheet:\n");
                try (InputStream sheet = sheets.next()) {
                    InputSource sheetSource = new InputSource(sheet);
                    parser.parse(sheetSource);
                }
                System.out.println();
            }
        }
    }

    /**
     * 获取xml读处理器
     * @param sst
     * @return
     * @throws SAXException
     * @throws ParserConfigurationException
     */
    private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException, ParserConfigurationException {
        XMLReader parser = SAXHelper.newXMLReader();
        ContentHandler handler = new MySheetHandler(sst, startRowNum ,endCellNum, datas);
        parser.setContentHandler(handler);
        return parser;
    }

    /**
     * 返回结果集
     * @return
     */
    public List<List<String>> getDatas(){
        return datas;
    }
}
